﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Configuration;
using System.Data.SqlClient;
namespace BuildForm
{
    public partial class Customers : Form
    {

        SqlConnection con;
        SqlCommand cmd;
        SqlParameter param;
        SqlDataAdapter da;
        DataSet ds;
        DataView dv;
        DataTable dt;
        public Customers()
        {
            try
            {
                InitializeComponent();
                initialconect();
                initialADOObject();
                LoadCustomerDisconect();
                initialform();
                cbSearchBy.SelectedIndex = 0;
            }
            catch 
            {
                MessageBox.Show("Connect Fail");
                return;
            }
        }
        #region Initial
        void initialconect()
        {
            con = new SqlConnection();
            con.ConnectionString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        }
        void initialform()
        {
            errorProvider1.Clear();
            btnAdd.Enabled = true;
            txtCustID.Enabled = false;
            txtCustID.Clear();
            txtCompanyName.Clear();
            txtcontactname.Clear();
            txtContactTitle.Clear();
            mtxtFax.Clear();
            cbCountry.SelectedIndex = -1;
            txtRegion.Clear();
            txtSearchName.Clear();
            txtAddress.Clear();
            txtCity.Clear();
            mtbPhone.Clear();
            mtbPostalCode.Clear();
            btnUpdate.Enabled = false;
            btnDelete.Enabled = false;

        } 
        #endregion
        #region LoadData
        void initialADOObject()
        {
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "SELECT * FROM Sales.Customers";
            da = new SqlDataAdapter(cmd);
           // cb = new SqlCommandBuilder(da);
            ds = new DataSet();
            da.FillSchema(ds, SchemaType.Mapped);
            da.Fill(ds, "Sales.Customers");
        }
        void LoadCustomerDisconect()
        {
            dt = new DataTable("Sales.Customers");
            dt = ds.Tables["Sales.Customers"];
            dv = new DataView(dt);
            gvCustomers.DataSource = dt;
        } 
        #endregion
        #region MyRegion:Add,Update,Delete
        void AddCustomer()
        {
            try
            {
                string tmp="";
                con.Open();
                cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_Customer_insert";
                param = new SqlParameter("@companyname", SqlDbType.NVarChar);
                param.Size = 40;
                param.Value = txtCompanyName.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@contactname", SqlDbType.NVarChar);
                param.Size = 30;
                param.Value = txtcontactname.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@contacttitle", SqlDbType.NVarChar);
                param.Size = 30;
                param.Value = txtContactTitle.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@address", SqlDbType.NVarChar);
                param.Size = 60;
                param.Value = txtAddress.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@city", SqlDbType.NVarChar);
                param.Size = 15;
                param.Value = txtCity.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@region", SqlDbType.NVarChar);
                param.Size = 15;
                tmp = txtRegion.Text.Trim();
                if (tmp == "")
                    param.Value = DBNull.Value;
                else
                param.Value = txtRegion.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@postalcode", SqlDbType.NVarChar);
                param.Size = 10;
                tmp = mtbPostalCode.Text.Trim();
                if (tmp == "")
                    param.Value = DBNull.Value;
                else
                param.Value = mtbPostalCode.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@country", SqlDbType.NVarChar);
                param.Size = 15;
                param.Value = cbCountry.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@phone", SqlDbType.NVarChar);
                param.Size = 24;
                param.Value = mtbPhone.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@fax", SqlDbType.NVarChar);
                param.Size = 24;
                tmp = mtxtFax.Text.Trim();
                if (tmp == "(   )    -")
                    param.Value = DBNull.Value;
                else
                param.Value = mtxtFax.Text;
                cmd.Parameters.Add(param);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
                initialADOObject();
                LoadCustomerDisconect();
                btnReload.Enabled = false;
            }
        }

        void DeleteCustomer()
        {
            try
            {
                con.Open();
                cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_Customer_Delete";
                param = new SqlParameter("@custid", SqlDbType.Int);
                param.Value = txtCustID.Text;
                cmd.Parameters.Add(param);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
                initialADOObject();
                LoadCustomerDisconect();
                btnReload.Enabled = false;
            }
        }
        void UpdateCustomer()
        {
            try
            {
                string tmp = "";
                con.Open();
                cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "dbo.sp_Customer_Update";
                param = new SqlParameter("@custid", SqlDbType.Int);
                param.Value = txtCustID.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@companyname", SqlDbType.NVarChar);
                param.Size = 40;
                param.Value = txtCompanyName.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@contactname", SqlDbType.NVarChar);
                param.Size = 30;
                param.Value = txtcontactname.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@contacttitle", SqlDbType.NVarChar);
                param.Size = 30;
                param.Value = txtContactTitle.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@address", SqlDbType.NVarChar);
                param.Size = 60;
                param.Value = txtAddress.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@city", SqlDbType.NVarChar);
                param.Size = 15;
                param.Value = txtCity.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@region", SqlDbType.NVarChar);
                param.Size = 15;
                tmp = txtRegion.Text.Trim();
                if (tmp == "")
                    param.Value = DBNull.Value;
                else
                    param.Value = txtRegion.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@postalcode", SqlDbType.NVarChar);
                param.Size = 10;
                tmp = mtbPostalCode.Text.Trim();
                if (tmp == "")
                    param.Value = DBNull.Value;
                else
                    param.Value = mtbPostalCode.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@country", SqlDbType.NVarChar);
                param.Size = 15;
                param.Value = cbCountry.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@phone", SqlDbType.NVarChar);
                param.Size = 24;
                param.Value = mtbPhone.Text;
                cmd.Parameters.Add(param);

                param = new SqlParameter("@fax", SqlDbType.NVarChar);
                param.Size = 24;
                tmp = mtxtFax.Text.Trim();
                if (tmp == "(   )    -")
                    param.Value = DBNull.Value;
                else
                    param.Value = mtxtFax.Text;
                cmd.Parameters.Add(param);

                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                con.Close();
                initialADOObject();
                LoadCustomerDisconect();
                btnReload.Enabled = false;
            }
        } 
        #endregion
        #region Validate
        bool validateinput()
        {
            try
            {
                Boolean error = false;
                string companyname = txtCompanyName.Text;
                if (companyname == "")
                {
                    errorProvider1.SetError(txtCompanyName, "Please enter Company Name");
                    error = true;
                }

                string contactname = txtcontactname.Text;
                if (contactname == "")
                {
                    errorProvider1.SetError(txtcontactname, "Please enter Contact Name");
                    error = true;
                }

                string contacttitle = txtContactTitle.Text;
                if (contacttitle == "")
                {
                    errorProvider1.SetError(txtContactTitle, "Please enter Contact Title");
                    error = true;
                }
                string Address = txtAddress.Text;
                if (Address == "")
                {
                    errorProvider1.SetError(txtAddress, "Please enter Address");
                    error = true;
                }
                string city = txtCity.Text;
                if (city == "")
                {
                    errorProvider1.SetError(txtCity, "Please enter City");
                    error = true;
                }

                string country = cbCountry.Text;
                if (country == "")
                {
                    errorProvider1.SetError(cbCountry, "Please enter Country");
                    error = true;
                }
                if (!(mtbPostalCode.Text.Trim() == ""))
                    if (!mtbPostalCode.MaskCompleted)
                    {
                        errorProvider1.SetError(mtbPostalCode, "Wrong input!");
                        error = true;
                    }
                if (mtxtFax.Text.Trim() != "(   )    -")
                    if (!mtxtFax.MaskCompleted)
                    {
                        errorProvider1.SetError(mtxtFax, "Wrong Input");
                        error = true;
                    }
                if (error) return false;
                else return true;
            }
            catch
            {
                return false;
            }

        }
        bool validatephone()
        {
            try
            {

                if (mtbPhone.Text.Trim() == "(   )    -")
                {
                    errorProvider1.SetError(mtbPhone, "Enter Phone Number");
                    return false;
                }

                if (!mtbPhone.MaskCompleted)
                {
                    errorProvider1.SetError(mtbPhone, "Wrong Input");
                    return false;
                }
            }
            catch
            {
                return false;
            }
            return true;
        }
        bool validate()
        {
            Boolean check1 = false, check2 = false;
            errorProvider1.Clear();
            if (validateinput())
                check1 = true;
            if (validatephone())
                check2 = true;
            return (check1 && check2);
        } 
        #endregion
        #region Event
        private void btnAdd_Click(object sender, EventArgs e)
        {
            Boolean check = true;
            if (validate())
            {
                try
                {
                    AddCustomer();
                }
                catch
                {
                    check = false;
                    MessageBox.Show("Have an error in conection to database. \nSo we can't add this tuple ! ", "Error");
                }
                finally
                {
                    initialform();
                }
            }
            else return;
            if (check) MessageBox.Show("Add Succsess!", "Notification");
        }
        private void btnDelete_Click(object sender, EventArgs e)
        {
            if (gvCustomers.SelectedRows.Count > 0)
            {
                Boolean check = true;
                try
                {
                    DialogResult rs = MessageBox.Show("Are You Sure !", "Waring", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
                    if (rs == DialogResult.Yes)
                        DeleteCustomer();
                    else return;
                }
                catch
                {
                    check = false;
                    MessageBox.Show("Sorry ! Cannot Delete This Rows", "Waring !");
                }
                finally
                {
                    gvCustomers.ClearSelection();
                    initialform();
                }
                if (check)
                {
                    MessageBox.Show("Susscess!", "Notification");
                }
            }
        }
        private void gvCustomers_SelectionChanged(object sender, EventArgs e)
        {
            errorProvider1.Clear();   
            if (gvCustomers.SelectedRows.Count > 0)
            {
                try
                {
                    btnAdd.Enabled = false;
                    btnDelete.Enabled = true;
                    btnUpdate.Enabled = true;
                    DataGridViewRow r = gvCustomers.SelectedRows[0];
                    txtCustID.Text = r.Cells[0].Value.ToString();
                    txtCompanyName.Text = r.Cells[1].Value.ToString();
                    txtcontactname.Text = r.Cells[2].Value.ToString();
                    txtContactTitle.Text = r.Cells[3].Value.ToString();
                    txtAddress.Text = r.Cells[4].Value.ToString();
                    txtCity.Text = r.Cells[5].Value.ToString();
                    txtRegion.Text = r.Cells[6].Value.ToString();
                    mtbPostalCode.Text = r.Cells[7].Value.ToString();
                    cbCountry.Text= r.Cells[8].Value.ToString();
                    mtbPhone.Text = r.Cells[9].Value.ToString();
                    mtxtFax.Text = r.Cells[10].Value.ToString();
                }
                catch (Exception ex)
                {

                    MessageBox.Show(ex.Message);
                }
            }
        }
        private void btnUpdate_Click(object sender, EventArgs e)
        {
            Boolean check = true;
            if (validate())
            {
                try
                {
                    DialogResult rs = MessageBox.Show("Are You Sure !", "Waring", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
                    if (rs == DialogResult.Yes)
                        UpdateCustomer();
                    else return;
                }
                catch
                {
                    check = false;
                    MessageBox.Show("Have an information are using by orther table! ", "Error");
                }
                finally
                {
                    gvCustomers.ClearSelection();
                    initialform();
                }
            }
            else return;
            if (check)
            {
                MessageBox.Show("Update Succsess!", "Notification");
            }
        }
        private void btnNew_Click(object sender, EventArgs e)
        {
            initialform();
            gvCustomers.ClearSelection();
        }
        private void btnSearch_Click(object sender, EventArgs e)
        {

            int tmp = cbSearchBy.SelectedIndex;
            if (tmp == 0)
            {
                MessageBox.Show("Please Choise Search by ?", "Notification");
                return;
            }
            if (tmp == 1)
                SearchCustomer();
            if (tmp == 2)
                SearchbyCountry();
            if (tmp == 3)
                SearchbyCity();
            if (txtSearchName.Text.Trim()!="")
            btnReload.Enabled=true;
        }
        private void tbtReload_Click(object sender, EventArgs e)
        {
            try
            {
                initialADOObject();
                LoadCustomerDisconect();
                btnReload.Enabled = false;
            }
            catch 
            {
                MessageBox.Show("Connect Fail !","Error");
            }
        }
        private void txtSearchName_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                btnSearch_Click(sender, e); ;
            }
        }
        #endregion
        #region Search
        void SearchCustomer()
        {
            try
            {
                dv = new DataView(dt);
                dv.RowFilter = string.Format("companyname LIKE'%{0}%'", txtSearchName.Text);
                gvCustomers.DataSource = dv;
            }
            catch 
            {
                MessageBox.Show("Search Fail !","Error");
            }
        }
        void SearchbyCountry()
        {
            try
            {
                dv = new DataView(dt);
                dv.RowFilter = string.Format("country like'%{0}%'", txtSearchName.Text);
                gvCustomers.DataSource = dv;
            }
            catch 
            {
                MessageBox.Show("Search Fail !","Error");
            }
        }
        void SearchbyCity()
        {
            try
            {
                dv = new DataView(dt);
                dv.RowFilter = string.Format("city like'%{0}%'", txtSearchName.Text);
                gvCustomers.DataSource = dv;
            }
            catch
            {
                MessageBox.Show("Search Fail !", "Error");
            }
        } 
        #endregion
    }
}
